{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "initial_id",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:45:08.263701900Z",
     "start_time": "2025-02-16T09:45:07.647249100Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pyecharts.charts import *\n",
    "from pyecharts import options as opts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c22ecf80fe52d820",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## 1加载数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f402d5d24b7ec3e3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:45:38.664653900Z",
     "start_time": "2025-02-16T09:45:36.785345800Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售</th>\n",
       "      <th>账单状态</th>\n",
       "      <th>账单周期</th>\n",
       "      <th>账单金额</th>\n",
       "      <th>开票金额</th>\n",
       "      <th>实收金额</th>\n",
       "      <th>未收金额</th>\n",
       "      <th>预计付款日</th>\n",
       "      <th>应付日期</th>\n",
       "      <th>商务催收日期</th>\n",
       "      <th>账期</th>\n",
       "      <th>实际到账日</th>\n",
       "      <th>开票日期</th>\n",
       "      <th>客服</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>s101</td>\n",
       "      <td>未确认</td>\n",
       "      <td>2019-05</td>\n",
       "      <td>29805.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>s102</td>\n",
       "      <td>未确认</td>\n",
       "      <td>2019-05</td>\n",
       "      <td>1572.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>s103</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05-16</td>\n",
       "      <td>a203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>s104</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05-08</td>\n",
       "      <td>a204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>s105</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05-10</td>\n",
       "      <td>a205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5252</th>\n",
       "      <td>s5353</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2017-07</td>\n",
       "      <td>22800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a5453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5253</th>\n",
       "      <td>s5354</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2017-07</td>\n",
       "      <td>6483.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-10-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a5454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5254</th>\n",
       "      <td>s5355</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-06</td>\n",
       "      <td>418795.0</td>\n",
       "      <td>418795.0</td>\n",
       "      <td>418795.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-08-14</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>11-02</td>\n",
       "      <td>a5455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5255</th>\n",
       "      <td>s5356</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-06</td>\n",
       "      <td>86337.0</td>\n",
       "      <td>86337.0</td>\n",
       "      <td>86337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-08-22</td>\n",
       "      <td>11-02</td>\n",
       "      <td>a5456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5256</th>\n",
       "      <td>s5357</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2017-06</td>\n",
       "      <td>34550.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a5457</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5257 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         销售 账单状态     账单周期      账单金额      开票金额      实收金额      未收金额       预计付款日  \\\n",
       "0      s101  未确认  2019-05   29805.0       NaN       NaN       NaN  2019-07-31   \n",
       "1      s102  未确认  2019-05    1572.6       NaN       NaN       NaN  2019-07-31   \n",
       "2      s103  已确认  2019-04  487551.2  487551.2       NaN  487551.2  2019-06-30   \n",
       "3      s104  已确认  2019-04  378835.0  378835.0       NaN  378835.0  2019-07-31   \n",
       "4      s105  已确认  2019-04  326866.0  326866.0       NaN  326866.0  2019-07-31   \n",
       "...     ...  ...      ...       ...       ...       ...       ...         ...   \n",
       "5252  s5353  已确认  2017-07   22800.0       NaN       NaN       NaN  2017-07-31   \n",
       "5253  s5354  已确认  2017-07    6483.0       NaN       NaN       NaN  2017-09-30   \n",
       "5254  s5355  已核销  2017-06  418795.0  418795.0  418795.0       0.0  2017-08-14   \n",
       "5255  s5356  已核销  2017-06   86337.0   86337.0   86337.0       0.0  2017-08-31   \n",
       "5256  s5357  已确认  2017-06   34550.0       NaN       NaN       NaN  2017-08-31   \n",
       "\n",
       "            应付日期      商务催收日期  账期       实际到账日   开票日期     客服  \n",
       "0     2019-07-31  2019-08-15  60         NaN    NaN   a201  \n",
       "1     2019-07-31  2019-08-15  60         NaN    NaN   a202  \n",
       "2     2019-06-30  2019-07-15  60         NaN  05-16   a203  \n",
       "3     2019-07-31  2019-08-15  90         NaN  05-08   a204  \n",
       "4     2019-07-31  2019-08-15  90         NaN  05-10   a205  \n",
       "...          ...         ...  ..         ...    ...    ...  \n",
       "5252  2017-08-31  2017-09-15  30         NaN    NaN  a5453  \n",
       "5253  2017-09-30  2017-10-15  60         NaN    NaN  a5454  \n",
       "5254  2017-08-31  2017-09-15  60  2017-08-31  11-02  a5455  \n",
       "5255  2017-08-31  2017-09-15  60  2017-08-22  11-02  a5456  \n",
       "5256  2017-08-31  2017-09-15  60         NaN    NaN  a5457  \n",
       "\n",
       "[5257 rows x 14 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('./data/业务数据.xlsx')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d1a95984be7c48fb",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5257 entries, 0 to 5256\n",
      "Data columns (total 14 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   销售      5257 non-null   object \n",
      " 1   账单状态    5257 non-null   object \n",
      " 2   账单周期    5257 non-null   object \n",
      " 3   账单金额    5257 non-null   float64\n",
      " 4   开票金额    5010 non-null   float64\n",
      " 5   实收金额    4470 non-null   float64\n",
      " 6   未收金额    5010 non-null   float64\n",
      " 7   预计付款日   5256 non-null   object \n",
      " 8   应付日期    5257 non-null   object \n",
      " 9   商务催收日期  5257 non-null   object \n",
      " 10  账期      5257 non-null   int64  \n",
      " 11  实际到账日   4387 non-null   object \n",
      " 12  开票日期    4996 non-null   object \n",
      " 13  客服      5257 non-null   object \n",
      "dtypes: float64(4), int64(1), object(9)\n",
      "memory usage: 575.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# #要使用原始数据构建新指标，所以保留原始数据，copy新的数据，在新的数据中创建新指标\n",
    "df1 = df.copy()\n",
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4a57873e79fb200c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:45:03.830530100Z",
     "start_time": "2025-02-16T08:45:03.811426900Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售</th>\n",
       "      <th>账单状态</th>\n",
       "      <th>账单周期</th>\n",
       "      <th>账单金额</th>\n",
       "      <th>开票金额</th>\n",
       "      <th>实收金额</th>\n",
       "      <th>未收金额</th>\n",
       "      <th>预计付款日</th>\n",
       "      <th>应付日期</th>\n",
       "      <th>商务催收日期</th>\n",
       "      <th>账期</th>\n",
       "      <th>实际到账日</th>\n",
       "      <th>开票日期</th>\n",
       "      <th>客服</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>s101</td>\n",
       "      <td>未确认</td>\n",
       "      <td>2019-05</td>\n",
       "      <td>29805.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>s102</td>\n",
       "      <td>未确认</td>\n",
       "      <td>2019-05</td>\n",
       "      <td>1572.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>s103</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05-16</td>\n",
       "      <td>a203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>s104</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05-08</td>\n",
       "      <td>a204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>s105</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05-10</td>\n",
       "      <td>a205</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     销售 账单状态     账单周期      账单金额      开票金额  实收金额      未收金额       预计付款日  \\\n",
       "0  s101  未确认  2019-05   29805.0       NaN   NaN       NaN  2019-07-31   \n",
       "1  s102  未确认  2019-05    1572.6       NaN   NaN       NaN  2019-07-31   \n",
       "2  s103  已确认  2019-04  487551.2  487551.2   NaN  487551.2  2019-06-30   \n",
       "3  s104  已确认  2019-04  378835.0  378835.0   NaN  378835.0  2019-07-31   \n",
       "4  s105  已确认  2019-04  326866.0  326866.0   NaN  326866.0  2019-07-31   \n",
       "\n",
       "         应付日期      商务催收日期  账期 实际到账日   开票日期    客服  \n",
       "0  2019-07-31  2019-08-15  60   NaN    NaN  a201  \n",
       "1  2019-07-31  2019-08-15  60   NaN    NaN  a202  \n",
       "2  2019-06-30  2019-07-15  60   NaN  05-16  a203  \n",
       "3  2019-07-31  2019-08-15  90   NaN  05-08  a204  \n",
       "4  2019-07-31  2019-08-15  90   NaN  05-10  a205  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c12adc8c11f8ad5e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:45:04.642143400Z",
     "start_time": "2025-02-16T08:45:04.604255600Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>账单金额</th>\n",
       "      <th>开票金额</th>\n",
       "      <th>实收金额</th>\n",
       "      <th>未收金额</th>\n",
       "      <th>账期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5.257000e+03</td>\n",
       "      <td>5.010000e+03</td>\n",
       "      <td>4.470000e+03</td>\n",
       "      <td>5.010000e+03</td>\n",
       "      <td>5257.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4.073241e+04</td>\n",
       "      <td>4.096896e+04</td>\n",
       "      <td>4.082419e+04</td>\n",
       "      <td>4.684636e+03</td>\n",
       "      <td>64.539661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>8.176172e+04</td>\n",
       "      <td>8.007245e+04</td>\n",
       "      <td>7.970628e+04</td>\n",
       "      <td>2.888464e+04</td>\n",
       "      <td>15.622765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2.500000e+01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>5.103000e+03</td>\n",
       "      <td>5.300000e+03</td>\n",
       "      <td>5.112250e+03</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>60.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.436500e+04</td>\n",
       "      <td>1.486560e+04</td>\n",
       "      <td>1.434000e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>60.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.178000e+04</td>\n",
       "      <td>4.220250e+04</td>\n",
       "      <td>4.170750e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>75.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.508796e+06</td>\n",
       "      <td>1.356215e+06</td>\n",
       "      <td>1.301665e+06</td>\n",
       "      <td>1.277098e+06</td>\n",
       "      <td>90.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               账单金额          开票金额          实收金额          未收金额           账期\n",
       "count  5.257000e+03  5.010000e+03  4.470000e+03  5.010000e+03  5257.000000\n",
       "mean   4.073241e+04  4.096896e+04  4.082419e+04  4.684636e+03    64.539661\n",
       "std    8.176172e+04  8.007245e+04  7.970628e+04  2.888464e+04    15.622765\n",
       "min    0.000000e+00  2.500000e+01  0.000000e+00  0.000000e+00     0.000000\n",
       "25%    5.103000e+03  5.300000e+03  5.112250e+03  0.000000e+00    60.000000\n",
       "50%    1.436500e+04  1.486560e+04  1.434000e+04  0.000000e+00    60.000000\n",
       "75%    4.178000e+04  4.220250e+04  4.170750e+04  0.000000e+00    75.000000\n",
       "max    1.508796e+06  1.356215e+06  1.301665e+06  1.277098e+06    90.000000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6282f0c885fd6bf0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:45:05.253500400Z",
     "start_time": "2025-02-16T08:45:05.231413400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2019-05-17 00:00:00')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 要确定以哪一天为基准计算 各种指标\n",
    "# 取出实际到账日最大的一天, 作为计算指标的基准日期\n",
    "today_time = pd.to_datetime(df1['实际到账日'].fillna(0)).max()\n",
    "today_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b3501c35583b4939",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:48:32.231430500Z",
     "start_time": "2025-02-16T08:48:32.199390800Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\86187\\AppData\\Local\\Temp\\ipykernel_22796\\2238429846.py:1: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  df1['实收金额'].fillna(0, inplace=True)\n",
      "C:\\Users\\86187\\AppData\\Local\\Temp\\ipykernel_22796\\2238429846.py:2: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  df1['未收金额'].fillna(0, inplace=True)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0      2019-05-17\n",
       "1      2019-05-17\n",
       "2      2019-05-17\n",
       "3      2019-05-17\n",
       "4      2019-05-17\n",
       "          ...    \n",
       "5252   2019-05-17\n",
       "5253   2019-05-17\n",
       "5254   2017-08-31\n",
       "5255   2017-08-22\n",
       "5256   2019-05-17\n",
       "Name: 实际到账日, Length: 5257, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['实收金额'].fillna(0, inplace=True)\n",
    "df1['未收金额'].fillna(0, inplace=True)\n",
    "df1['账单周期'] = pd.to_datetime(df1['账单周期'])\n",
    "df1['应付日期'] = pd.to_datetime(df1['应付日期'])\n",
    "df1['实际到账日'] = pd.to_datetime(df1['实际到账日']).fillna(today_time)\n",
    "# df1['应付日期']\n",
    "df1['实际到账日']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c82d4451d5ea23cb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:48:42.734957100Z",
     "start_time": "2025-02-16T08:48:42.723755400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5257 entries, 0 to 5256\n",
      "Data columns (total 14 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   销售      5257 non-null   object        \n",
      " 1   账单状态    5257 non-null   object        \n",
      " 2   账单周期    5257 non-null   datetime64[ns]\n",
      " 3   账单金额    5257 non-null   float64       \n",
      " 4   开票金额    5010 non-null   float64       \n",
      " 5   实收金额    5257 non-null   float64       \n",
      " 6   未收金额    5257 non-null   float64       \n",
      " 7   预计付款日   5256 non-null   object        \n",
      " 8   应付日期    5257 non-null   datetime64[ns]\n",
      " 9   商务催收日期  5257 non-null   object        \n",
      " 10  账期      5257 non-null   int64         \n",
      " 11  实际到账日   5257 non-null   datetime64[ns]\n",
      " 12  开票日期    4996 non-null   object        \n",
      " 13  客服      5257 non-null   object        \n",
      "dtypes: datetime64[ns](3), float64(4), int64(1), object(6)\n",
      "memory usage: 575.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4b173efe93a85b5d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:49:21.099475900Z",
     "start_time": "2025-02-16T08:49:21.087434Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n要计算的指标\\n    - 每个季度账单金额和坏账率（逾期90天以上）\\n所有未收金额/所有账单金额\\n未收金额 = 账单金额-实收金额\\n\\n    - 每个季度60天账期入催率，90天账单入催率\\n\\n    - 不同逾期天数的回款情况\\n历史逾期天数： 有逾期，已经还完了\\n当前逾期天数： 现在还欠着钱，没还完\\n'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "要计算的指标\n",
    "    - 每个季度账单金额和坏账率（逾期90天以上）\n",
    "所有未收金额/所有账单金额\n",
    "未收金额 = 账单金额-实收金额\n",
    "\n",
    "    - 每个季度60天账期入催率，90天账单入催率\n",
    "\n",
    "    - 不同逾期天数的回款情况\n",
    "历史逾期天数： 有逾期，已经还完了\n",
    "当前逾期天数： 现在还欠着钱，没还完\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "148739b4186aed80",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## 2 创建逾期字段"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1821ab660908f6b3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:52:06.993079300Z",
     "start_time": "2025-02-16T08:52:06.984099500Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['销售', '账单状态', '账单周期', '账单金额', '开票金额', '实收金额', '未收金额', '预计付款日', '应付日期',\n",
       "       '商务催收日期', '账期', '实际到账日', '开票日期', '客服'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "为了后续计算，在原始数据基础上构造新的字段：\n",
    "- 是否逾期，是否逾期90天，未收金额2（校验原始数据中的未收金额），当前逾期天数，历史逾期天数\n",
    "\"\"\"\n",
    "df1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f7158aa840c717c5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:52:14.170349100Z",
     "start_time": "2025-02-16T08:52:14.156987400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2019-05-17 00:00:00')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "today_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cc60816d88f59eea",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:53:33.592862100Z",
     "start_time": "2025-02-16T08:53:33.580902400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       0\n",
       "1       0\n",
       "2       0\n",
       "3       0\n",
       "4       0\n",
       "       ..\n",
       "5252    1\n",
       "5253    1\n",
       "5254    1\n",
       "5255    1\n",
       "5256    1\n",
       "Name: 是否到期, Length: 5257, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 如果应付日期 >2019-05-17 \n",
    "df1['是否到期'] = df1['应付日期'].apply(lambda x: 0 if x > today_time else 1)\n",
    "df1['是否到期']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "27a0e1342c48daaf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:54:09.846572100Z",
     "start_time": "2025-02-16T08:54:09.826159200Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "是否到期\n",
       "1    4437\n",
       "0     820\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['是否到期'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a6e60bc0e6cee99a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:54:26.282791500Z",
     "start_time": "2025-02-16T08:54:26.250355800Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "账单周期\n",
       "2018-11-01    428\n",
       "2018-12-01    418\n",
       "2019-03-01    416\n",
       "2018-10-01    391\n",
       "2019-04-01    382\n",
       "2018-08-01    370\n",
       "2019-01-01    358\n",
       "2018-07-01    332\n",
       "2018-09-01    320\n",
       "2018-06-01    307\n",
       "2018-05-01    279\n",
       "2019-02-01    240\n",
       "2018-04-01    222\n",
       "2018-03-01    167\n",
       "2018-01-01    124\n",
       "2017-12-01    103\n",
       "2018-02-01    102\n",
       "2017-11-01     74\n",
       "2017-09-01     58\n",
       "2017-10-01     55\n",
       "2017-07-01     55\n",
       "2017-08-01     51\n",
       "2017-06-01      3\n",
       "2019-05-01      2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['账单周期'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3675aff74e4bb41e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:55:12.267163300Z",
     "start_time": "2025-02-16T08:55:12.238943600Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       0\n",
       "1       0\n",
       "2       0\n",
       "3       0\n",
       "4       0\n",
       "       ..\n",
       "5252    1\n",
       "5253    1\n",
       "5254    1\n",
       "5255    1\n",
       "5256    1\n",
       "Name: 是否到期90天, Length: 5257, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 是否逾期90天\n",
    "df1['是否到期90天'] = (today_time - df1['应付日期']).apply(lambda x: 1 if x.days>=90 else 0)\n",
    "df1['是否到期90天']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "15dbfe290974bc1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:55:21.884785600Z",
     "start_time": "2025-02-16T08:55:21.872641400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "是否到期90天\n",
       "1    3374\n",
       "0    1883\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['是否到期90天'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d8112c5cf96fb2c9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:56:33.411347900Z",
     "start_time": "2025-02-16T08:56:33.392281Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "未收金额2\n",
       "0.0         4452\n",
       "2150.0         4\n",
       "1900.0         3\n",
       "8150.0         2\n",
       "11200.0        2\n",
       "            ... \n",
       "10700.0        1\n",
       "11500.0        1\n",
       "22800.0        1\n",
       "6483.0         1\n",
       "185467.5       1\n",
       "Name: count, Length: 779, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['未收金额2'] = (df1['账单金额']-df1['实收金额'])\n",
    "# 未收金额=0 历史逾期  没有逾期 (实际到账 <=应付日期)/有逾期 实际到账> 应付日期 \n",
    "# 未收金额>0 当前逾期  today_time - 应付日期\n",
    "df1['未收金额2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "20b61443869c6805",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:57:57.686990400Z",
     "start_time": "2025-02-16T08:57:57.594981800Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "历史逾期天数\n",
       " 0      345\n",
       "-44     329\n",
       "-2      307\n",
       "-1      302\n",
       "-14     268\n",
       "       ... \n",
       "-70       1\n",
       " 90       1\n",
       " 548      1\n",
       " 88       1\n",
       " 80       1\n",
       "Name: count, Length: 199, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['历史逾期天数'] = df1.apply(lambda x : (x['实际到账日'] - x['应付日期']).days if x['未收金额2'] == 0 else (today_time - x['应付日期']).days,axis = 1)\n",
    "df1['历史逾期天数'].value_counts()\n",
    "# 历史逾期天数 算出来是负数或者0 说明没有逾期 正数才是逾期的情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d05015887393de18",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:58:33.953469700Z",
     "start_time": "2025-02-16T08:58:33.900095500Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "当前逾期天数\n",
       " 0      4452\n",
       "-44      319\n",
       "-14      232\n",
       "-75       67\n",
       "-29       36\n",
       " 17       34\n",
       "-59       18\n",
       " 2        16\n",
       " 47       16\n",
       " 78        9\n",
       " 32        7\n",
       " 106       5\n",
       " 351       5\n",
       " 137       4\n",
       " 168       3\n",
       " 471       3\n",
       " 259       2\n",
       " 321       2\n",
       " 412       2\n",
       " 198       2\n",
       " 290       2\n",
       " 563       2\n",
       " 594       2\n",
       " 624       2\n",
       " 533       2\n",
       " 443       2\n",
       " 91        1\n",
       " 153       1\n",
       " 63        1\n",
       " 122       1\n",
       " 428       1\n",
       " 275       1\n",
       " 382       1\n",
       " 518       1\n",
       " 502       1\n",
       " 487       1\n",
       " 548       1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['当前逾期天数'] = df1.apply(lambda x : x['历史逾期天数'] if x['未收金额2'] > 0 else 0, axis = 1)\n",
    "df1['当前逾期天数'].value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "74e8135ef4659829",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T08:58:49.008386400Z",
     "start_time": "2025-02-16T08:58:48.980433Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售</th>\n",
       "      <th>账单状态</th>\n",
       "      <th>账单周期</th>\n",
       "      <th>账单金额</th>\n",
       "      <th>开票金额</th>\n",
       "      <th>实收金额</th>\n",
       "      <th>未收金额</th>\n",
       "      <th>预计付款日</th>\n",
       "      <th>应付日期</th>\n",
       "      <th>商务催收日期</th>\n",
       "      <th>账期</th>\n",
       "      <th>实际到账日</th>\n",
       "      <th>开票日期</th>\n",
       "      <th>客服</th>\n",
       "      <th>是否到期</th>\n",
       "      <th>是否到期90天</th>\n",
       "      <th>未收金额2</th>\n",
       "      <th>历史逾期天数</th>\n",
       "      <th>当前逾期天数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>s101</td>\n",
       "      <td>未确认</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>29805.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29805.0</td>\n",
       "      <td>-75</td>\n",
       "      <td>-75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>s102</td>\n",
       "      <td>未确认</td>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>1572.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a202</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1572.6</td>\n",
       "      <td>-75</td>\n",
       "      <td>-75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>s103</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>05-16</td>\n",
       "      <td>a203</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>487551.2</td>\n",
       "      <td>-44</td>\n",
       "      <td>-44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>s104</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>90</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>05-08</td>\n",
       "      <td>a204</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>378835.0</td>\n",
       "      <td>-75</td>\n",
       "      <td>-75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>s105</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-07-31</td>\n",
       "      <td>2019-08-15</td>\n",
       "      <td>90</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>05-10</td>\n",
       "      <td>a205</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326866.0</td>\n",
       "      <td>-75</td>\n",
       "      <td>-75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5252</th>\n",
       "      <td>s5353</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>22800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>30</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a5453</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>22800.0</td>\n",
       "      <td>624</td>\n",
       "      <td>624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5253</th>\n",
       "      <td>s5354</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>6483.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-10-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a5454</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6483.0</td>\n",
       "      <td>594</td>\n",
       "      <td>594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5254</th>\n",
       "      <td>s5355</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-06-01</td>\n",
       "      <td>418795.0</td>\n",
       "      <td>418795.0</td>\n",
       "      <td>418795.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-08-14</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>11-02</td>\n",
       "      <td>a5455</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5255</th>\n",
       "      <td>s5356</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-06-01</td>\n",
       "      <td>86337.0</td>\n",
       "      <td>86337.0</td>\n",
       "      <td>86337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-08-22</td>\n",
       "      <td>11-02</td>\n",
       "      <td>a5456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5256</th>\n",
       "      <td>s5357</td>\n",
       "      <td>已确认</td>\n",
       "      <td>2017-06-01</td>\n",
       "      <td>34550.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>2017-09-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a5457</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>34550.0</td>\n",
       "      <td>624</td>\n",
       "      <td>624</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5257 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         销售 账单状态       账单周期      账单金额      开票金额      实收金额      未收金额  \\\n",
       "0      s101  未确认 2019-05-01   29805.0       NaN       0.0       0.0   \n",
       "1      s102  未确认 2019-05-01    1572.6       NaN       0.0       0.0   \n",
       "2      s103  已确认 2019-04-01  487551.2  487551.2       0.0  487551.2   \n",
       "3      s104  已确认 2019-04-01  378835.0  378835.0       0.0  378835.0   \n",
       "4      s105  已确认 2019-04-01  326866.0  326866.0       0.0  326866.0   \n",
       "...     ...  ...        ...       ...       ...       ...       ...   \n",
       "5252  s5353  已确认 2017-07-01   22800.0       NaN       0.0       0.0   \n",
       "5253  s5354  已确认 2017-07-01    6483.0       NaN       0.0       0.0   \n",
       "5254  s5355  已核销 2017-06-01  418795.0  418795.0  418795.0       0.0   \n",
       "5255  s5356  已核销 2017-06-01   86337.0   86337.0   86337.0       0.0   \n",
       "5256  s5357  已确认 2017-06-01   34550.0       NaN       0.0       0.0   \n",
       "\n",
       "           预计付款日       应付日期      商务催收日期  账期      实际到账日   开票日期     客服  是否到期  \\\n",
       "0     2019-07-31 2019-07-31  2019-08-15  60 2019-05-17    NaN   a201     0   \n",
       "1     2019-07-31 2019-07-31  2019-08-15  60 2019-05-17    NaN   a202     0   \n",
       "2     2019-06-30 2019-06-30  2019-07-15  60 2019-05-17  05-16   a203     0   \n",
       "3     2019-07-31 2019-07-31  2019-08-15  90 2019-05-17  05-08   a204     0   \n",
       "4     2019-07-31 2019-07-31  2019-08-15  90 2019-05-17  05-10   a205     0   \n",
       "...          ...        ...         ...  ..        ...    ...    ...   ...   \n",
       "5252  2017-07-31 2017-08-31  2017-09-15  30 2019-05-17    NaN  a5453     1   \n",
       "5253  2017-09-30 2017-09-30  2017-10-15  60 2019-05-17    NaN  a5454     1   \n",
       "5254  2017-08-14 2017-08-31  2017-09-15  60 2017-08-31  11-02  a5455     1   \n",
       "5255  2017-08-31 2017-08-31  2017-09-15  60 2017-08-22  11-02  a5456     1   \n",
       "5256  2017-08-31 2017-08-31  2017-09-15  60 2019-05-17    NaN  a5457     1   \n",
       "\n",
       "      是否到期90天     未收金额2  历史逾期天数  当前逾期天数  \n",
       "0           0   29805.0     -75     -75  \n",
       "1           0    1572.6     -75     -75  \n",
       "2           0  487551.2     -44     -44  \n",
       "3           0  378835.0     -75     -75  \n",
       "4           0  326866.0     -75     -75  \n",
       "...       ...       ...     ...     ...  \n",
       "5252        1   22800.0     624     624  \n",
       "5253        1    6483.0     594     594  \n",
       "5254        1       0.0       0       0  \n",
       "5255        1       0.0      -9       0  \n",
       "5256        1   34550.0     624     624  \n",
       "\n",
       "[5257 rows x 19 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "9e0319f5e56cea34",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:00:34.221425200Z",
     "start_time": "2025-02-16T09:00:34.143943200Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "账单季度\n",
       "2018Q4    1237\n",
       "2018Q3    1022\n",
       "2019Q1    1014\n",
       "2018Q2     808\n",
       "2018Q1     393\n",
       "2019Q2     384\n",
       "2017Q4     232\n",
       "2017Q3     164\n",
       "2017Q2       3\n",
       "Freq: Q-DEC, Name: count, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把数据转换成季度\n",
    "df1['账单季度'] = df1['账单周期'].apply(lambda x: x.to_period('Q'))\n",
    "df1['账单季度'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c741a9c228afa06",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:01:52.991746700Z",
     "start_time": "2025-02-16T09:01:52.971296800Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3856, 20)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 从数据中截取 2017年3季度到2018年4季度的数据\n",
    "df2 = df1[(df1['账单季度'] <= '2018Q4') & (df1['账单季度'] >= '2017Q3')]\n",
    "df2.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b33c29f6a7ecdc9",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## 3 每个季度账单金额和坏账率（逾期90天以上）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "5f13b4506edcbcba",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:04:56.417395300Z",
     "start_time": "2025-02-16T09:04:56.394902700Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>账单金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>8247952.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>11643604.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>17149674.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>31097661.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>38292071.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>51963089.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               账单金额\n",
       "账单季度               \n",
       "2017Q3   8247952.62\n",
       "2017Q4  11643604.99\n",
       "2018Q1  17149674.79\n",
       "2018Q2  31097661.29\n",
       "2018Q3  38292071.12\n",
       "2018Q4  51963089.64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fn1 = df2.groupby('账单季度')[['账单金额']].sum()\n",
    "fn1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "af21ff46d6be1942",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:08:44.077338100Z",
     "start_time": "2025-02-16T09:08:44.060770600Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>到期金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>8247952.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>11643604.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>17149674.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>31097661.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>38292071.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>28265677.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               到期金额\n",
       "账单季度               \n",
       "2017Q3   8247952.62\n",
       "2017Q4  11643604.99\n",
       "2018Q1  17149674.79\n",
       "2018Q2  31097661.29\n",
       "2018Q3  38292071.12\n",
       "2018Q4  28265677.59"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算逾期90天\n",
    "df3 = df2[df2['是否到期90天'] == 1]\n",
    "fn2 = df3.groupby('账单季度')[['账单金额']].sum()\n",
    "fn2.columns = ['到期金额']\n",
    "fn2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ac26217775f35ab2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:10:23.670430100Z",
     "start_time": "2025-02-16T09:10:23.648905300Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>当前逾期90+金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>63883.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>57380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>64283.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>106930.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>412920.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>304183.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        当前逾期90+金额\n",
       "账单季度             \n",
       "2017Q3    63883.0\n",
       "2017Q4    57380.0\n",
       "2018Q1    64283.0\n",
       "2018Q2   106930.0\n",
       "2018Q3   412920.1\n",
       "2018Q4   304183.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fn3 = df3.groupby('账单季度')[['未收金额2']].sum()\n",
    "fn3.columns = ['当前逾期90+金额']\n",
    "fn3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f377ccbc0b87da42",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:10:38.517231500Z",
     "start_time": "2025-02-16T09:10:38.491330500Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>账单金额</th>\n",
       "      <th>到期金额</th>\n",
       "      <th>当前逾期90+金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>8247952.62</td>\n",
       "      <td>8247952.62</td>\n",
       "      <td>63883.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>11643604.99</td>\n",
       "      <td>11643604.99</td>\n",
       "      <td>57380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>17149674.79</td>\n",
       "      <td>17149674.79</td>\n",
       "      <td>64283.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>31097661.29</td>\n",
       "      <td>31097661.29</td>\n",
       "      <td>106930.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>38292071.12</td>\n",
       "      <td>38292071.12</td>\n",
       "      <td>412920.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>51963089.64</td>\n",
       "      <td>28265677.59</td>\n",
       "      <td>304183.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               账单金额         到期金额  当前逾期90+金额\n",
       "账单季度                                       \n",
       "2017Q3   8247952.62   8247952.62    63883.0\n",
       "2017Q4  11643604.99  11643604.99    57380.0\n",
       "2018Q1  17149674.79  17149674.79    64283.0\n",
       "2018Q2  31097661.29  31097661.29   106930.0\n",
       "2018Q3  38292071.12  38292071.12   412920.1\n",
       "2018Q4  51963089.64  28265677.59   304183.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 3个df合并到一起\n",
    "final1 = pd.concat([fn1, fn2, fn3], axis = 1)\n",
    "final1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7b110c4b8e36c094",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:11:54.879009800Z",
     "start_time": "2025-02-16T09:11:54.852396200Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>账单金额</th>\n",
       "      <th>到期金额</th>\n",
       "      <th>当前逾期90+金额</th>\n",
       "      <th>90+净坏账率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>8247952.62</td>\n",
       "      <td>8247952.62</td>\n",
       "      <td>63883.0</td>\n",
       "      <td>0.008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>11643604.99</td>\n",
       "      <td>11643604.99</td>\n",
       "      <td>57380.0</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>17149674.79</td>\n",
       "      <td>17149674.79</td>\n",
       "      <td>64283.0</td>\n",
       "      <td>0.004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>31097661.29</td>\n",
       "      <td>31097661.29</td>\n",
       "      <td>106930.0</td>\n",
       "      <td>0.003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>38292071.12</td>\n",
       "      <td>38292071.12</td>\n",
       "      <td>412920.1</td>\n",
       "      <td>0.011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>51963089.64</td>\n",
       "      <td>28265677.59</td>\n",
       "      <td>304183.0</td>\n",
       "      <td>0.011</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               账单金额         到期金额  当前逾期90+金额  90+净坏账率\n",
       "账单季度                                                \n",
       "2017Q3   8247952.62   8247952.62    63883.0    0.008\n",
       "2017Q4  11643604.99  11643604.99    57380.0    0.005\n",
       "2018Q1  17149674.79  17149674.79    64283.0    0.004\n",
       "2018Q2  31097661.29  31097661.29   106930.0    0.003\n",
       "2018Q3  38292071.12  38292071.12   412920.1    0.011\n",
       "2018Q4  51963089.64  28265677.59   304183.0    0.011"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final1['90+净坏账率'] = round(final1['当前逾期90+金额'] / final1['到期金额'], 3)\n",
    "final1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "3e00bce4b607e812",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:12:44.093095900Z",
     "start_time": "2025-02-16T09:12:44.056036600Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'E:\\\\python_code\\\\artificial_intelligence\\\\金融风控项目与数据挖掘\\\\render.html'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bar = (\n",
    "\tBar() # 柱状图\n",
    "\t.add_xaxis(list(final1.index.values.astype(str)))  # 柱状图X坐标的值 \n",
    "\t.add_yaxis(\"账单金额\",list(final1.账单金额),yaxis_index=0,color=\"#5793f3\")    # 柱状图y坐标的值 y坐标索引(yaxis_index) 颜色 \n",
    "\t.set_global_opts(title_opts=opts.TitleOpts(title=\"90+净坏账率\"))\n",
    "\t.extend_axis(  # 添加一个右侧的Y轴\n",
    "\t\tyaxis=opts.AxisOpts(\n",
    "\t\t\tname=\"90+净坏账率\",type_=\"value\",min_=0,max_=0.014,\n",
    "\t\t\tposition=\"right\",\n",
    "\t\t\taxisline_opts=opts.AxisLineOpts(\n",
    "\t\t\tlinestyle_opts=opts.LineStyleOpts(color=\"#d14a61\")\n",
    "\t\t\t),\n",
    "\t\t\taxislabel_opts=opts.LabelOpts(formatter=\"{value}\"),\n",
    "\t\t)\n",
    "\t)\n",
    ")\n",
    "line = (  # 折线图\n",
    "\tLine()\n",
    "\t.add_xaxis(list(final1.index.values.astype(str)))\n",
    "\t.add_yaxis(\"90+净坏账率\",list(final1['90+净坏账率']),yaxis_index=1,\n",
    "\t\tcolor=\"#675bba\",label_opts=opts.LabelOpts(is_show=False),\n",
    "\t)\n",
    ")\n",
    "bar.overlap(line).render()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e60eb338e84100a",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## 4 每个季度的60天账单入催率，90天账单入催率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "903e089c66b4a8a7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:19:13.145441700Z",
     "start_time": "2025-02-16T09:19:13.130880300Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>60天账期的账单金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q2</th>\n",
       "      <td>539682.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>4854770.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>6737327.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>12106356.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>19234086.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>22830710.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>26337959.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019Q1</th>\n",
       "      <td>12590574.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         60天账期的账单金额\n",
       "账单季度               \n",
       "2017Q2    539682.00\n",
       "2017Q3   4854770.94\n",
       "2017Q4   6737327.99\n",
       "2018Q1  12106356.79\n",
       "2018Q2  19234086.87\n",
       "2018Q3  22830710.42\n",
       "2018Q4  26337959.52\n",
       "2019Q1  12590574.08"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = df1[(df1['账期'] == 60) & (df1['是否到期'] == 1)]\n",
    "fn1 = df4.groupby('账单季度')[['账单金额']].sum()\n",
    "fn1.columns=['60天账期的账单金额']\n",
    "fn1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "bfd5d4c37c7fedf7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:20:05.904910100Z",
     "start_time": "2025-02-16T09:20:05.871445100Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>60天账期的入催金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q2</th>\n",
       "      <td>34550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>36983.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>52750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>62460.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>13590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>380265.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>584789.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019Q1</th>\n",
       "      <td>1007968.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        60天账期的入催金额\n",
       "账单季度              \n",
       "2017Q2     34550.0\n",
       "2017Q3     36983.0\n",
       "2017Q4     52750.0\n",
       "2018Q1     62460.0\n",
       "2018Q2     13590.0\n",
       "2018Q3    380265.1\n",
       "2018Q4    584789.5\n",
       "2019Q1   1007968.0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5 = df1[(df1['账期'] == 60) & (df1['是否到期'] == 1) & (df1['历史逾期天数'] > 0)]\n",
    "fn2 = df5.groupby('账单季度')[['未收金额2']].sum()\n",
    "fn2.columns=['60天账期的入催金额']\n",
    "fn2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "f6783ab9c5941b2a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:20:45.769132100Z",
     "start_time": "2025-02-16T09:20:45.744504400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>90天账期的入催金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>1900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>8235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>325141.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019Q1</th>\n",
       "      <td>694579.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        90天账期的入催金额\n",
       "账单季度              \n",
       "2017Q3      1900.0\n",
       "2017Q4         0.0\n",
       "2018Q1       800.0\n",
       "2018Q2         0.0\n",
       "2018Q3      8235.0\n",
       "2018Q4    325141.0\n",
       "2019Q1    694579.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6 = df1[(df1['账期'] == 90) & (df1['是否到期'] == 1)]\n",
    "fn3 = df6.groupby('账单季度')[['账单金额']].sum()\n",
    "fn3.columns=['90天账期的账单金额']\n",
    "df7 = df1[(df1['账期'] == 90) & (df1['是否到期'] == 1) & (df1['历史逾期天数'] > 0)]\n",
    "fn4 = df7.groupby('账单季度')[['未收金额2']].sum()\n",
    "fn4.columns=['90天账期的入催金额']\n",
    "fn4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "f94a727f1802ad4e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:21:07.955556Z",
     "start_time": "2025-02-16T09:21:07.936763100Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>60天账期的账单金额</th>\n",
       "      <th>60天账期的入催金额</th>\n",
       "      <th>90天账期的账单金额</th>\n",
       "      <th>90天账期的入催金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q2</th>\n",
       "      <td>539682.00</td>\n",
       "      <td>34550.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>4854770.94</td>\n",
       "      <td>36983.0</td>\n",
       "      <td>2769264.0</td>\n",
       "      <td>1900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>6737327.99</td>\n",
       "      <td>52750.0</td>\n",
       "      <td>3921491.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>12106356.79</td>\n",
       "      <td>62460.0</td>\n",
       "      <td>4244304.0</td>\n",
       "      <td>800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>19234086.87</td>\n",
       "      <td>13590.0</td>\n",
       "      <td>8427775.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>22830710.42</td>\n",
       "      <td>380265.1</td>\n",
       "      <td>9835629.0</td>\n",
       "      <td>8235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>26337959.52</td>\n",
       "      <td>584789.5</td>\n",
       "      <td>17706430.0</td>\n",
       "      <td>325141.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019Q1</th>\n",
       "      <td>12590574.08</td>\n",
       "      <td>1007968.0</td>\n",
       "      <td>7219632.0</td>\n",
       "      <td>694579.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         60天账期的账单金额  60天账期的入催金额  90天账期的账单金额  90天账期的入催金额\n",
       "账单季度                                                   \n",
       "2017Q2    539682.00     34550.0         NaN         NaN\n",
       "2017Q3   4854770.94     36983.0   2769264.0      1900.0\n",
       "2017Q4   6737327.99     52750.0   3921491.0         0.0\n",
       "2018Q1  12106356.79     62460.0   4244304.0       800.0\n",
       "2018Q2  19234086.87     13590.0   8427775.0         0.0\n",
       "2018Q3  22830710.42    380265.1   9835629.0      8235.0\n",
       "2018Q4  26337959.52    584789.5  17706430.0    325141.0\n",
       "2019Q1  12590574.08   1007968.0   7219632.0    694579.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final2 = pd.concat([fn1,fn2,fn3,fn4],axis=1)\n",
    "final2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "df96d644c42592e6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:21:51.029848100Z",
     "start_time": "2025-02-16T09:21:50.974295100Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>60天账期的账单金额</th>\n",
       "      <th>60天账期的入催金额</th>\n",
       "      <th>90天账期的账单金额</th>\n",
       "      <th>90天账期的入催金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>4854770.94</td>\n",
       "      <td>36983.0</td>\n",
       "      <td>2769264.0</td>\n",
       "      <td>1900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>6737327.99</td>\n",
       "      <td>52750.0</td>\n",
       "      <td>3921491.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>12106356.79</td>\n",
       "      <td>62460.0</td>\n",
       "      <td>4244304.0</td>\n",
       "      <td>800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>19234086.87</td>\n",
       "      <td>13590.0</td>\n",
       "      <td>8427775.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>22830710.42</td>\n",
       "      <td>380265.1</td>\n",
       "      <td>9835629.0</td>\n",
       "      <td>8235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>26337959.52</td>\n",
       "      <td>584789.5</td>\n",
       "      <td>17706430.0</td>\n",
       "      <td>325141.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         60天账期的账单金额  60天账期的入催金额  90天账期的账单金额  90天账期的入催金额\n",
       "账单季度                                                   \n",
       "2017Q3   4854770.94     36983.0   2769264.0      1900.0\n",
       "2017Q4   6737327.99     52750.0   3921491.0         0.0\n",
       "2018Q1  12106356.79     62460.0   4244304.0       800.0\n",
       "2018Q2  19234086.87     13590.0   8427775.0         0.0\n",
       "2018Q3  22830710.42    380265.1   9835629.0      8235.0\n",
       "2018Q4  26337959.52    584789.5  17706430.0    325141.0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final2 = final2[(final2.index <= '2018Q4') & (final2.index >= '2017Q3')]\n",
    "final2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "5a7379bab324eda",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:22:23.846156200Z",
     "start_time": "2025-02-16T09:22:23.798512400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\86187\\AppData\\Local\\Temp\\ipykernel_22796\\3184585494.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final2['60天账期的入催率'] = final2['60天账期的入催金额'] / final2['60天账期的账单金额']\n",
      "C:\\Users\\86187\\AppData\\Local\\Temp\\ipykernel_22796\\3184585494.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final2['90天账期的入催率'] = final2['90天账期的入催金额'] / final2['90天账期的账单金额']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>60天账期的账单金额</th>\n",
       "      <th>60天账期的入催金额</th>\n",
       "      <th>90天账期的账单金额</th>\n",
       "      <th>90天账期的入催金额</th>\n",
       "      <th>60天账期的入催率</th>\n",
       "      <th>90天账期的入催率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>账单季度</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017Q3</th>\n",
       "      <td>4854770.94</td>\n",
       "      <td>36983.0</td>\n",
       "      <td>2769264.0</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>0.007618</td>\n",
       "      <td>0.000686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017Q4</th>\n",
       "      <td>6737327.99</td>\n",
       "      <td>52750.0</td>\n",
       "      <td>3921491.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.007830</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>12106356.79</td>\n",
       "      <td>62460.0</td>\n",
       "      <td>4244304.0</td>\n",
       "      <td>800.0</td>\n",
       "      <td>0.005159</td>\n",
       "      <td>0.000188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>19234086.87</td>\n",
       "      <td>13590.0</td>\n",
       "      <td>8427775.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000707</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q3</th>\n",
       "      <td>22830710.42</td>\n",
       "      <td>380265.1</td>\n",
       "      <td>9835629.0</td>\n",
       "      <td>8235.0</td>\n",
       "      <td>0.016656</td>\n",
       "      <td>0.000837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q4</th>\n",
       "      <td>26337959.52</td>\n",
       "      <td>584789.5</td>\n",
       "      <td>17706430.0</td>\n",
       "      <td>325141.0</td>\n",
       "      <td>0.022203</td>\n",
       "      <td>0.018363</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         60天账期的账单金额  60天账期的入催金额  90天账期的账单金额  90天账期的入催金额  60天账期的入催率  90天账期的入催率\n",
       "账单季度                                                                         \n",
       "2017Q3   4854770.94     36983.0   2769264.0      1900.0   0.007618   0.000686\n",
       "2017Q4   6737327.99     52750.0   3921491.0         0.0   0.007830   0.000000\n",
       "2018Q1  12106356.79     62460.0   4244304.0       800.0   0.005159   0.000188\n",
       "2018Q2  19234086.87     13590.0   8427775.0         0.0   0.000707   0.000000\n",
       "2018Q3  22830710.42    380265.1   9835629.0      8235.0   0.016656   0.000837\n",
       "2018Q4  26337959.52    584789.5  17706430.0    325141.0   0.022203   0.018363"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final2['60天账期的入催率'] = final2['60天账期的入催金额'] / final2['60天账期的账单金额']\n",
    "final2['90天账期的入催率'] = final2['90天账期的入催金额'] / final2['90天账期的账单金额']\n",
    "final2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "f013ea3c049d6a20",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:22:51.812110100Z",
     "start_time": "2025-02-16T09:22:51.785424600Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'E:\\\\python_code\\\\artificial_intelligence\\\\金融风控项目与数据挖掘\\\\render.html'"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "line = (\n",
    "\tLine()\n",
    "\t.add_xaxis(list(final2.index.values.astype(str)))\n",
    "\t.add_yaxis(\"60天账期入催率\",list(final2['60天账期的入催率']),yaxis_index=0,\n",
    "\t\t\tcolor=\"#675bba\",label_opts=opts.LabelOpts(is_show=False),)\n",
    "\t.set_global_opts(title_opts=opts.TitleOpts(title=\"不同账期入催率\"),)\n",
    "\t.add_xaxis(list(final1.index.values.astype(str)))\n",
    "\t.add_yaxis(\n",
    "\t\t\"90天账期入催率\",list(final2['90天账期的入催率']),yaxis_index=0,\n",
    "\t\tcolor=\"#d14a61\",label_opts=opts.LabelOpts(is_show=False),)\n",
    "\t)\n",
    "line.render()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5660088de6e63b8",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## 回收了的账单的逾期情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "f6137f8cc679008d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:23:56.433210100Z",
     "start_time": "2025-02-16T09:23:56.397934700Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售</th>\n",
       "      <th>账单状态</th>\n",
       "      <th>账单周期</th>\n",
       "      <th>账单金额</th>\n",
       "      <th>开票金额</th>\n",
       "      <th>实收金额</th>\n",
       "      <th>未收金额</th>\n",
       "      <th>预计付款日</th>\n",
       "      <th>应付日期</th>\n",
       "      <th>商务催收日期</th>\n",
       "      <th>账期</th>\n",
       "      <th>实际到账日</th>\n",
       "      <th>开票日期</th>\n",
       "      <th>客服</th>\n",
       "      <th>是否到期</th>\n",
       "      <th>是否到期90天</th>\n",
       "      <th>未收金额2</th>\n",
       "      <th>历史逾期天数</th>\n",
       "      <th>当前逾期天数</th>\n",
       "      <th>账单季度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1411</th>\n",
       "      <td>s1512</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>279710.0</td>\n",
       "      <td>279710.0</td>\n",
       "      <td>279710.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-04-04</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>90</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>11-29</td>\n",
       "      <td>a1612</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2018Q4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1412</th>\n",
       "      <td>s1513</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>2248.0</td>\n",
       "      <td>2248.0</td>\n",
       "      <td>2248.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-03-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>01-30</td>\n",
       "      <td>a1613</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>2018Q4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1413</th>\n",
       "      <td>s1514</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>98550.0</td>\n",
       "      <td>98550.0</td>\n",
       "      <td>98550.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-03-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2019-02-27</td>\n",
       "      <td>12-17</td>\n",
       "      <td>a1614</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>2018Q4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1414</th>\n",
       "      <td>s1515</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>62820.0</td>\n",
       "      <td>62820.0</td>\n",
       "      <td>62820.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>90</td>\n",
       "      <td>2019-03-11</td>\n",
       "      <td>12-25</td>\n",
       "      <td>a1615</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20</td>\n",
       "      <td>0</td>\n",
       "      <td>2018Q4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1415</th>\n",
       "      <td>s1516</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>26290.0</td>\n",
       "      <td>26290.0</td>\n",
       "      <td>26290.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>90</td>\n",
       "      <td>2019-03-11</td>\n",
       "      <td>12-25</td>\n",
       "      <td>a1616</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20</td>\n",
       "      <td>0</td>\n",
       "      <td>2018Q4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5245</th>\n",
       "      <td>s5346</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>17300.0</td>\n",
       "      <td>17300.0</td>\n",
       "      <td>17300.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-10-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-10-23</td>\n",
       "      <td>10-27</td>\n",
       "      <td>a5446</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>2017Q3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5246</th>\n",
       "      <td>s5347</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>168760.0</td>\n",
       "      <td>168760.0</td>\n",
       "      <td>168760.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>2017-11-15</td>\n",
       "      <td>90</td>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>10-27</td>\n",
       "      <td>a5447</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2017Q3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5247</th>\n",
       "      <td>s5348</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>454319.0</td>\n",
       "      <td>454319.0</td>\n",
       "      <td>454319.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-10-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-11-22</td>\n",
       "      <td>10-27</td>\n",
       "      <td>a5448</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>2017Q3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5248</th>\n",
       "      <td>s5349</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>48450.0</td>\n",
       "      <td>48450.0</td>\n",
       "      <td>48450.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-10-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-09-29</td>\n",
       "      <td>10-27</td>\n",
       "      <td>a5449</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>2017Q3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5249</th>\n",
       "      <td>s5350</td>\n",
       "      <td>已核销</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>12450.0</td>\n",
       "      <td>12450.0</td>\n",
       "      <td>12450.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>2017-10-15</td>\n",
       "      <td>60</td>\n",
       "      <td>2017-10-11</td>\n",
       "      <td>11-07</td>\n",
       "      <td>a5450</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>2017Q3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3793 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         销售 账单状态       账单周期      账单金额      开票金额      实收金额  未收金额       预计付款日  \\\n",
       "1411  s1512  已核销 2018-12-01  279710.0  279710.0  279710.0   0.0  2019-04-04   \n",
       "1412  s1513  已核销 2018-12-01    2248.0    2248.0    2248.0   0.0  2019-04-30   \n",
       "1413  s1514  已核销 2018-12-01   98550.0   98550.0   98550.0   0.0  2019-02-28   \n",
       "1414  s1515  已核销 2018-12-01   62820.0   62820.0   62820.0   0.0  2019-03-31   \n",
       "1415  s1516  已核销 2018-12-01   26290.0   26290.0   26290.0   0.0  2019-03-31   \n",
       "...     ...  ...        ...       ...       ...       ...   ...         ...   \n",
       "5245  s5346  已核销 2017-07-01   17300.0   17300.0   17300.0   0.0  2017-07-31   \n",
       "5246  s5347  已核销 2017-07-01  168760.0  168760.0  168760.0   0.0  2017-10-31   \n",
       "5247  s5348  已核销 2017-07-01  454319.0  454319.0  454319.0   0.0  2017-11-30   \n",
       "5248  s5349  已核销 2017-07-01   48450.0   48450.0   48450.0   0.0  2017-09-30   \n",
       "5249  s5350  已核销 2017-07-01   12450.0   12450.0   12450.0   0.0  2017-09-30   \n",
       "\n",
       "           应付日期      商务催收日期  账期      实际到账日   开票日期     客服  是否到期  是否到期90天  \\\n",
       "1411 2019-03-31  2019-04-15  90 2019-04-03  11-29  a1612     1        0   \n",
       "1412 2019-02-28  2019-03-15  60 2019-04-29  01-30  a1613     1        0   \n",
       "1413 2019-02-28  2019-03-15  60 2019-02-27  12-17  a1614     1        0   \n",
       "1414 2019-03-31  2019-04-15  90 2019-03-11  12-25  a1615     1        0   \n",
       "1415 2019-03-31  2019-04-15  90 2019-03-11  12-25  a1616     1        0   \n",
       "...         ...         ...  ..        ...    ...    ...   ...      ...   \n",
       "5245 2017-09-30  2017-10-15  60 2017-10-23  10-27  a5446     1        1   \n",
       "5246 2017-10-31  2017-11-15  90 2017-11-01  10-27  a5447     1        1   \n",
       "5247 2017-09-30  2017-10-15  60 2017-11-22  10-27  a5448     1        1   \n",
       "5248 2017-09-30  2017-10-15  60 2017-09-29  10-27  a5449     1        1   \n",
       "5249 2017-09-30  2017-10-15  60 2017-10-11  11-07  a5450     1        1   \n",
       "\n",
       "      未收金额2  历史逾期天数  当前逾期天数    账单季度  \n",
       "1411    0.0       3       0  2018Q4  \n",
       "1412    0.0      60       0  2018Q4  \n",
       "1413    0.0      -1       0  2018Q4  \n",
       "1414    0.0     -20       0  2018Q4  \n",
       "1415    0.0     -20       0  2018Q4  \n",
       "...     ...     ...     ...     ...  \n",
       "5245    0.0      23       0  2017Q3  \n",
       "5246    0.0       1       0  2017Q3  \n",
       "5247    0.0      53       0  2017Q3  \n",
       "5248    0.0      -1       0  2017Q3  \n",
       "5249    0.0      11       0  2017Q3  \n",
       "\n",
       "[3793 rows x 20 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df8 = df2[(df2['未收金额2'] == 0) & (df2['是否到期'] == 1)]\n",
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "e99d3553476adaf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:24:31.956443300Z",
     "start_time": "2025-02-16T09:24:31.925910400Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    3793.000000\n",
       "mean        4.350910\n",
       "std        43.300838\n",
       "min      -123.000000\n",
       "25%        -8.000000\n",
       "50%        -1.000000\n",
       "75%         8.000000\n",
       "max       471.000000\n",
       "Name: 历史逾期天数, dtype: float64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df8['历史逾期天数'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "bd9ddcde222d3f6b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:25:10.452288900Z",
     "start_time": "2025-02-16T09:25:10.419684900Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\86187\\AppData\\Local\\Temp\\ipykernel_22796\\3616814420.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df8['历史逾期天数2'] = pd.cut(df8['历史逾期天数'], bins=[-999,0,7,15,30,60,90,999], labels=['0','1-7','8-15','16-30','31-60','61-90','90+'])\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "历史逾期天数2\n",
       "0        2400\n",
       "1-7       440\n",
       "8-15      368\n",
       "16-30     281\n",
       "31-60     156\n",
       "90+        88\n",
       "61-90      60\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df8['历史逾期天数2'] = pd.cut(df8['历史逾期天数'], bins=[-999,0,7,15,30,60,90,999], labels=['0','1-7','8-15','16-30','31-60','61-90','90+'])\n",
    "df8['历史逾期天数2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "bb8e0df11e14f997",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:25:51.273771900Z",
     "start_time": "2025-02-16T09:25:51.235069300Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\86187\\AppData\\Local\\Temp\\ipykernel_22796\\4281535174.py:1: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.\n",
      "  final3 = df8.groupby('历史逾期天数2')[['账期']].count()\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>回收账单数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>历史逾期天数2</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-7</th>\n",
       "      <td>440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8-15</th>\n",
       "      <td>368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16-30</th>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31-60</th>\n",
       "      <td>156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61-90</th>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90+</th>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         回收账单数\n",
       "历史逾期天数2       \n",
       "0         2400\n",
       "1-7        440\n",
       "8-15       368\n",
       "16-30      281\n",
       "31-60      156\n",
       "61-90       60\n",
       "90+         88"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final3 = df8.groupby('历史逾期天数2')[['账期']].count()\n",
    "final3.columns=['回收账单数']\n",
    "final3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "7e2b80049ab11a45",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-02-16T09:26:06.357176700Z",
     "start_time": "2025-02-16T09:26:06.323427Z"
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'E:\\\\python_code\\\\artificial_intelligence\\\\金融风控项目与数据挖掘\\\\render.html'"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ydata = final3['回收账单数'].values.tolist()\n",
    "bar = (\n",
    "\tBar()\n",
    "\t.add_xaxis(list(final3.index.values.tolist()))\n",
    "\t.add_yaxis(\"收回账单数\",ydata,yaxis_index=0,color=\"#675bba\")\n",
    "\t.set_global_opts(\n",
    "\t\ttitle_opts=opts.TitleOpts(title=\"不同逾期天数的已收回账单数\"),\n",
    "\t)\n",
    ")\n",
    "bar.render()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ad8f95ec277bb5c",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
